package com.example.dao;


import com.example.entity.PurchaseRecord;
import com.example.util.DBUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class PurchaseRecordDao {

    public void add(PurchaseRecord record) {
        String sql = "INSERT INTO purchase_record(customer_id,guitar_id,price,purchase_time) VALUES(?,?,?,?)";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, record.getCustomerId());
            pstmt.setInt(2, record.getGuitarId());
            pstmt.setDouble(3, record.getPrice());
            pstmt.setTimestamp(4, new Timestamp(record.getPurchaseTime().getTime()));
            pstmt.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public void delete(int customerId, int guitarId) {
        String sql = "DELETE FROM purchase_record WHERE customer_id=? AND guitar_id=?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, customerId);
            pstmt.setInt(2, guitarId);
            pstmt.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public List<PurchaseRecord> findAll() {
        List<PurchaseRecord> records = new ArrayList<>();
        String sql = "SELECT pr.*, c.username as customer_name, g.brand as guitar_brand " +
                "FROM purchase_record pr " +
                "JOIN customer c ON pr.customer_id = c.id " +
                "JOIN guitar g ON pr.guitar_id = g.id";
        try (Connection conn = DBUtil.getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            while (rs.next()) {
                records.add(extractPurchaseRecordFromResultSet(rs));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return records;
    }

    public List<PurchaseRecord> findByCustomerId(int customerId) {
        List<PurchaseRecord> records = new ArrayList<>();
        String sql = "SELECT pr.*, c.username as customer_name, g.brand as guitar_brand " +
                "FROM purchase_record pr " +
                "JOIN customer c ON pr.customer_id = c.id " +
                "JOIN guitar g ON pr.guitar_id = g.id " +
                "WHERE pr.customer_id = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, customerId);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                records.add(extractPurchaseRecordFromResultSet(rs));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return records;
    }

    public List<PurchaseRecord> findRecentByCustomerId(int customerId, int limit) {
        List<PurchaseRecord> records = new ArrayList<>();
        String sql = "SELECT pr.*, c.username as customer_name, g.brand as guitar_brand " +
                "FROM purchase_record pr " +
                "JOIN customer c ON pr.customer_id = c.id " +
                "JOIN guitar g ON pr.guitar_id = g.id " +
                "WHERE pr.customer_id = ? " +
                "ORDER BY pr.purchase_time DESC LIMIT ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, customerId);
            pstmt.setInt(2, limit);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                records.add(extractPurchaseRecordFromResultSet(rs));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return records;
    }

    private PurchaseRecord extractPurchaseRecordFromResultSet(ResultSet rs) throws SQLException {
        PurchaseRecord record = new PurchaseRecord();
        record.setCustomerId(rs.getInt("customer_id"));
        record.setGuitarId(rs.getInt("guitar_id"));
        record.setPrice(rs.getDouble("price"));
        record.setPurchaseTime(rs.getTimestamp("purchase_time"));
        record.setCustomerName(rs.getString("customer_name"));
        record.setGuitarBrand(rs.getString("guitar_brand"));
        return record;
    }
} 